在OE的order_items和product_infortation資料表中,依照產品被訂購次數由多至少查看產品名稱和被訂購次數,從未被訂購商品的名稱也要查看。
說明:
CREATE OR REPLACE VIEW product_sale_vu
(product_name, sale_amount)
AS 子查詢;
SELECT product_id, COUNT(*) item_cnt
FROM order_items
GROUP BY product_id
CREATE OR REPLACE VIEW product_sale_vu
(product_name, sale_amount) AS
SELECT p.product_name, NVL(i.item_cnt,0)
FROM (SELECT product_id, COUNT(*) item_cnt
FROM order_items
GROUP BY product_id) i
RIGHT OUTER JOIN product_information p
ON i.product_id = p.product_id
order by 2 desc;
搜尋:
SELECT *
FROM product_sale_vu
WHERE sale_amount>16;
最後一篇了!希望有幫助到對資料庫有疑問的人,有緣再見!